const express = require('express');
const mysql = require('mysql');
const path = require('path');
const app = express();

const conn = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    port: 9999,
    database : 'sfl_db'
});
conn.connect();

// conn.query(`SELECT updaterecord.*,COUNT(uservotes.Verdict = 'bad' OR NULL) AS Bad,COUNT(uservotes.Verdict = 'good' OR NULL) AS Good FROM sfl_db.updaterecord,sfl_db.uservotes WHERE updaterecord.Id = uservotes.Update_id AND updaterecord.Ver_Id = (SELECT versionlib.Id FROM sfl_db.versionlib WHERE versionlib.VersionName = 'Studio3.0' AND versionlib.Soft_Id = (SELECT soft.Id FROM sfl_db.soft WHERE soft.SoftName = 'Studio_Ver')) GROUP BY updaterecord.Id`, function (error, results, fields) {
//     if (error) throw error;
//     for (const res in results) {
//         results[res].CreatedAt = moment(results[res].CreatedAt).format('YYYY-MM-DD hh:mm:ss');
//         //console.log(res);
//     }
//     // const tm = results[0].CreatedAt;
//     // console.log(moment(tm).format('YYYY-MM-DD hh:mm:ss'))
//     console.log(results);
// });


app.get('/', (req, res) => {
    res.send('Hello world');
});

app.use('/version',express.static(path.join(__dirname, 'version')))

app.listen(8083, () => {
    console.log('Server is running at http://localhost:8083')
})

// 登录获取所有发布软件
app.get('/login', (req, res) => {
    conn.query(`SELECT * FROM sfl_db.user WHERE Name = '${req.query.username}' AND Password = '${req.query.password}' `, function (error, results, fields) {
        if (error) throw error;
        if (results.length > 0)
            res.send("Success");
    });
});
